package com.balloon.io;

import com.balloon.logic.UserBean;
import com.balloon.logic.City;
import com.balloon.logic.UserComments;
import com.balloon.logic.UserVisitedPlace;
import java.sql.*;

/**
 * This class is used for handling data between UserBean and database.
 */
public class UserDAO {

    static Connection currentCon = null;
    static ResultSet rs = null;

    /**
     * This method is used for handling login process . It will write data into the database
     * @param bean a reference of UserBean
     * @return the proceeded reference of UserBean
     */
    public static UserBean login(UserBean bean) {

        // Preparing some objects for connection
        Statement stmt = null;

        //  Get the username and password from the bean
        // (Initially entered by the user and saved to the bean by the Servlet)
        String password = bean.getPassword();
        String userEmail = bean.getEmailAddress();

        // Build the query  SELECT * FROM users WHERE username='...' AND password= '...'
        String searchQuery =
                "select * from person where email='"
                + userEmail
                + "' AND password='"
                + password
                + "'";

        try {
            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            // Save the results of the query to the ResultSet 'rs'
            rs = stmt.executeQuery(searchQuery);
            // If 'rs' is empty, then more = false; else, more = true
            boolean more = rs.next();
            // If the ResultSet is empty >>> user does not exist >>> set the isValid variable to false
            if (!more) {
                System.out.println("Sorry, you are not a registered user! Please sign up first");
                bean.setValid(false);
            } // If the ResultSet is not empty >>> user exists >>> set the isValid variable to true
            // And save the user's First and Last names to the bean (To be displayed later by userLogged.jsp)
            else if (more) {
                String firstName = rs.getString("FirstName");
                String lastName = rs.getString("LastName");
                System.out.println("Welcome " + firstName);
                bean.setFirstName(firstName);
                bean.setLastName(lastName);
                bean.setValid(true);
            }
        } catch (Exception ex) {
            System.out.println("Log In failed: An Exception has occurred! " + ex);
        } //Some exception handling
        finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                }
                stmt = null;
            }

            if (currentCon != null) {

                try {
                    currentCon.close();
                } catch (Exception e) {
                }
                currentCon = null;

            }
        }
        return bean;

    }

    /**
     * This method is used for handling register process . It will write data into the database
     * @param bean a reference of UserBean
     */
    public static void register(UserBean bean) {
        String userEmail = bean.getEmailAddress();
        String password = bean.getPassword();
        String firstName = bean.getFirstName();
        String lastName = bean.getLastName();

        Statement stmt = null;

        // Build the query  SELECT * FROM users WHERE username='...' AND password= '...'
        String insertQuery =
                "INSERT INTO person (firstName,lastName,password,email) VALUES ('"
                + firstName + "','"
                + lastName + "','"
                + password + "','"
                + userEmail + "');";

        // "System.out.println" prints in the console; Normally used to trace the process
        System.out.println("Your user name is " + userEmail);
        System.out.println("Your password is " + password);
        System.out.println("Your email is" + firstName);
        System.out.println("Query: " + insertQuery);

        try {

            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();


            // Save the results of the query to the ResultSet 'rs'
            int insertrs = stmt.executeUpdate(insertQuery);
        } catch (Exception ex) {
            System.out.println("Log In failed: An Exception has occurred! " + ex);
        }
    }

    public static ResultSet getComments(String email) {
        Statement stmt = null;
        String selectQuery = "select city.cityName,citycomment.description from city,citycomment where city.cityID=citycomment.cityID and citycomment.person='" + email + "'";
        System.out.println(selectQuery);
        try {
            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            rs = stmt.executeQuery(selectQuery); // Save the results of the query to the ResultSet 'rs'
            return rs;
        } catch (Exception ex) {
            System.out.println("Get comments failed: An Exception has occurred! " + ex);
            return null;
        }
    }

    public static ResultSet getVisitedPlaces(String email) {
        Statement stmt = null;
        String selectQuery = "select placeCategory, visitedTime from visitedplace where person='" + email + "'";
        System.out.println(selectQuery);
        try {
            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            rs = stmt.executeQuery(selectQuery); // Save the results of the query to the ResultSet 'rs'
            return rs;
        } catch (Exception ex) {
            System.out.println("Get comments failed: An Exception has occurred! " + ex);
            return null;
        }
    }

    /**
     * This method will be used for adding visitedPlaces into the database
     * 
     * @param email User email
     * @param userVisitedPlace the user visited place
     */
    public static void addVisitedPlaces(String email, UserVisitedPlace userVisitedPlace) {
        try {
            currentCon = ConnectionManager.getConnection();
            Statement stmt = currentCon.createStatement();
            String object = userVisitedPlace.getObject();
            String insertString = "INSERT INTO visitedplace (person,placeCategory,placeID,visitedTime) VALUES('" + email + "'," + "'" + object + "'," + "1" + ",'" + userVisitedPlace.getTime() + "');";
            System.out.println(insertString);
            stmt.executeUpdate(insertString);
        } catch (SQLException ex) {
            System.err.println("Make places failed: An Exception has occurred! " + ex);
        }
    }

    /**
     * This method will be used for adding comments into the database
     *
     * @param email User email
     * @param userComments the comments user makes
     */
    public static void addComments(String email, UserComments userComments) {
        Statement stmt = null;
        int type = userComments.getType();
        String object = userComments.getObject();
        if (type == 1) {
            String selectQuery = "SELECT cityID from city where cityName='" + object + "';";
            int cityID = 0;
            try {
                //connect to DB
                currentCon = ConnectionManager.getConnection();
                stmt = currentCon.createStatement();
                // Save the results of the query to the ResultSet 'rs'
                rs = stmt.executeQuery(selectQuery);
                if (rs.next()) {
                    cityID = Integer.parseInt(rs.getString("cityID"));
                }

                String insertString = "INSERT INTO citycomment (cityID,person,description,postTime) VALUES ("
                        + cityID + ",'" + email + "','" + userComments.getDescription() + "','" + new java.sql.Time(System.currentTimeMillis()) + "');";
                stmt.executeUpdate(insertString);
            } catch (Exception ex) {
                System.err.println("Make comments failed: An Exception has occurred! " + ex);
            }
        } //Add comments for sighspot
        else if (type == 2) {
            String selectQuery = "SELECT sightSportID from city where cityName='" + object + "';";
            System.out.println(selectQuery);
            int cityID = 0;
            try {
                //connect to DB
                currentCon = ConnectionManager.getConnection();
                stmt = currentCon.createStatement();
                rs = stmt.executeQuery(selectQuery); // Save the results of the query to the ResultSet 'rs'
                if (rs.next()) {
                    cityID = Integer.parseInt(rs.getString("cityID"));
                }

                String insertString = "INSERT INTO citycomment (cityID,person,description,postTime) VALUES ("
                        + cityID + ",'" + email + "','" + userComments.getDescription() + "','" + new java.sql.Time(System.currentTimeMillis()) + "');";
                System.out.println(insertString);
                stmt.executeUpdate(insertString);
            } catch (Exception ex) {
                System.err.println("Make comments failed: An Exception has occurred! " + ex);
            }
        } else {
            String selectQuery = "SELECT cityID from city where cityName='" + object + "';";
            System.out.println(selectQuery);
            int cityID = 0;
            try {
                //connect to DB
                currentCon = ConnectionManager.getConnection();
                stmt = currentCon.createStatement();
                rs = stmt.executeQuery(selectQuery); // Save the results of the query to the ResultSet 'rs'
                if (rs.next()) {
                    cityID = Integer.parseInt(rs.getString("cityID"));
                }

                String insertString = "INSERT INTO citycomment (cityID,person,description,postTime) VALUES ("
                        + cityID + ",'" + email + "','" + userComments.getDescription() + "','" + new java.sql.Time(System.currentTimeMillis()) + "');";
                System.out.println(insertString);
                stmt.executeUpdate(insertString);

                stmt.executeUpdate(insertString);

            } catch (Exception ex) {
                System.err.println("Make comments failed: An Exception has occurred! " + ex);
            }
        }
    }

    /**
     * This method is used for add rank into the data.
     * @param city City user wants to rank
     */
    public static void setRating(City city) {
        Statement stmt = null;

        int cityID = city.getcityID();
        int rank = city.getRank();

        // Build the query  SELECT * FROM users WHERE username='...' AND password= '...'
        String insertQuery =
                "INSERT INTO cityRank (cityID,rank) VALUES ('"
                + cityID + "','"
                + rank + "');";

        try {
            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            // Save the results of the query to the ResultSet 'rs'
            int insertrs = stmt.executeUpdate(insertQuery);
        } catch (Exception ex) {
            System.out.println("Log In failed: An Exception has occurred! " + ex);
        }


    }
}



